create or replace package AC_WARNINGS is

  -- Author  : LEVWORK
  -- Created : 25/05/2012 11:38:04 PM
  -- Purpose : 
  
 procedure app_warnings(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number);

end AC_WARNINGS;
/

create or replace package body AC_WARNINGS is

 procedure app_warn_regions(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id   number;
 begin

   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Regions with the same displ. point (page:seq)' , p_sort, true, null );
   
   for r in (  select 
               nvl(t1.page_id, t2.page_id) page_id,
               nvl(t1.display_sequence, t2.display_sequence) display_sequence,
               t1.num num1, t2.num num2
              from
              ( select pr.page_id, pr.display_sequence, count(*) num 
                from apex_application_page_regions pr
                 where pr.application_id = pi_app_id1
                group by pr.page_id, pr.display_sequence
                having count(*) > 1
              ) t1 full outer join
              ( select pr.page_id, pr.display_sequence, count(*) num 
                from apex_application_page_regions pr
                 where pr.application_id = pi_app_id2
                group by pr.page_id, pr.display_sequence
                having count(*) > 1
              ) t2 ON t1.page_id = t2.page_id 
                  and t1.display_sequence = t2.display_sequence
              order by  nvl(t1.page_id, t2.page_id),
                        nvl(t1.display_sequence, t2.display_sequence)
            )
    loop
         insert into ac_results
          (result_id,     parent_id,     param_name,                                param_val_1, param_val_2, sort,
           help_text,      
           src_val_1,     src_val_2,  is_branch, is_src)
         values
          (p_result_id,   v_parent_id, r.page_id ||':'|| r.display_sequence  ,    r.num1,      r.num2, 1,
           null, 
           null,       null,  'N',       'N');
    end loop;
   
 end app_warn_regions;

 procedure app_warn_branch(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id   number;
 begin

   v_parent_id := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Branches with the same sequence (page:branch_point:process_sequence)' , p_sort, true, null );
   
   for r in (  select 
                nvl( t1.page_id,          t2.page_id         ) page_id,
                nvl( t1.BRANCH_POINT,     t2.BRANCH_POINT    ) BRANCH_POINT,
                nvl( t1.process_sequence, t2.process_sequence) process_sequence,
                t1.num num_1, 
                t2.num num_2
              from
              (   select pb.page_id, pb.BRANCH_POINT, 
                         pb.process_sequence, count(*) num
                  from APEX_APPLICATION_PAGE_BRANCHES pb
                  where pb.application_id = pi_app_id1
                  group by pb.page_id, 
                           pb.BRANCH_POINT,
                           pb.process_sequence
                  having count(*) > 1         
              ) t1  full outer join
              (   select pb.page_id, pb.BRANCH_POINT, 
                         pb.process_sequence, count(*) num
                  from APEX_APPLICATION_PAGE_BRANCHES pb
                  where pb.application_id = pi_app_id2
                  group by pb.page_id, 
                           pb.BRANCH_POINT,
                           pb.process_sequence
                  having count(*) > 1         
              ) t2
              ON   t1.page_id = t2.page_id 
               and t1.BRANCH_POINT = t2.BRANCH_POINT
               and t1.process_sequence = t2.process_sequence
              order by t1.page_id, 
                       t1.BRANCH_POINT,
                       t1.process_sequence 
            )
    loop
         insert into ac_results
          (result_id,     parent_id,     param_name,                                param_val_1, param_val_2, sort,
           help_text,      
           src_val_1,     src_val_2,  is_branch, is_src)
         values
          (p_result_id,   v_parent_id, r.page_id ||':'|| r.BRANCH_POINT||':'||r.process_sequence  ,    r.num_1,  r.num_2, 1,
           null, 
           null,       null,  'N',       'N');
    end loop;
   
 end app_warn_branch;

 procedure app_warnings(p_result_id number, pi_parent_id number, pi_app_id1 number, pi_app_id2 number, p_sort number) is
 v_parent_id number;  
 begin
   app_apex.setMsgValue(p_result_id, 'Warnings ...');
   v_parent_id  := ac_utils.ins_result_msg(p_result_id,  pi_parent_id, 'Warnings' , p_sort, true, ac_utils.G_ALERT_WARNING );
   app_warn_regions(p_result_id, v_parent_id, pi_app_id1, pi_app_id2, p_sort);
   app_warn_branch(p_result_id , v_parent_id, pi_app_id1, pi_app_id2, p_sort);
 end  app_warnings;

 
end AC_WARNINGS;
/

